from logging import log
from sys import modules
from typing import Text
from PyQt5 import QtSql
from PyQt5.QtSql import QSql, QSqlQuery
from functools import singledispatch
from log import logger

#单例模式
def singleton(cls):
    _instance = {}

    def inner():
        if cls not in _instance:
            _instance[cls] = cls()
        return _instance[cls]
    return inner

@singleton    
class DataBase():
    def __init__(self) -> None:
        self.database = QtSql.QSqlDatabase.addDatabase('QSQLITE')
        self.database.setDatabaseName('./database/info.db')
        if self.database.open() == False:
            logger.error('fail to open database')
        self.query = QSqlQuery(self.database)

class InfoSql():
    def __init__(self) -> None:
        self.dataBase = DataBase()
        self.query = self.dataBase.query
        
    #查询一条病例数据
    def searchOneRow(self,patientId):
        sql = "select * from patientInfo where ID = {}".format(str(patientId))
        self.query.exec_(sql)
        self.query.next()
        oneRow = []
        if self.query.isValid() == False:
            return oneRow
        for i in range(24):
            if self.query.isNull(i) == True:
                oneRow.append(' ')
            else:
                oneRow.append(self.query.value(i))
        return oneRow
   
    #读取所有数据
    def readAllRow(self):
        sql = "select * from patientInfo"
        self.query.exec_(sql)
        allRows = []
        while self.query.next():
            row = []
            for i in range(24):
                row.append(self.query.value(i))
            allRows.append(row)
        return allRows

    #删除一条病例数据
    def delOneRow(self,id):
        sql = 'delete from patientInfo where ID = {}'.format(str(id))
        return self.query.exec_(sql)

    #插入一条病例数据
    def insertOneRow(self,patientList:list):
        tmpStr = ''
        for i in range(len(patientList)):
            if patientList[i] == '':
                tmpStr += 'Null,'
            elif i == 0 or i == 2 or i == 3:
                tmpStr += "'" + patientList[i] + "',"
            else:
                tmpStr += patientList[i] + ","
        tmpStr = tmpStr.rstrip(',')
        sql = 'insert into patientInfo values({})'.format(tmpStr)
        logger.info(sql)
        return self.query.exec_(sql)

    #重载：插入一条数据，以id形式
    def insertOneNullRow(self,id:str):
        tmpstr = str(id) + ','
        for i in range(23):
            tmpstr += 'Null,'
        tmpstr = tmpstr.strip(',')
        sql = 'insert into patientInfo values({})'.format(str(tmpstr))
        logger.info(sql)
        return self.query.exec_(sql)
        


    #修改一个单元的数据
    def modifyOneCell(self,id,colName,value):
        value = "'{}'".format(value)
        sql = "update patientInfo set {1} = {2} where ID = {0}".format(str(id),str(colName),str(value))
        logger.info(sql)
        return self.query.exec_(sql)


class ImagePathSql():
    def __init__(self) -> None:
        self.dataBase = DataBase()
        self.query = self.dataBase.query

    #读取所有数据
    def readAllRow(self):
        sql = "select * from ImagePath"
        self.query.exec_(sql)
        allRows = []
        while self.query.next():
            row = []
            for i in range(2):
                row.append(self.query.value(i))
            allRows.append(row)
        return allRows        
    #根据id查询一条数据
    def searchOneRow(self,id):
        sql = "select * from ImagePath where ID = {}".format("'" + str(id) + "'")
        logger.info(sql)
        self.query.exec_(sql)
        self.query.next()
        oneRow = []
        if self.query.isValid() == False:
            return oneRow
        for i in range(2):
            if self.query.isNull(i) == True:
                oneRow.append(' ')
            else:
                oneRow.append(self.query.value(i))
        logger.debug(oneRow)
        return oneRow

    #往数据库插入一条数据
    def insertOneRow(self,row:list):
        tmpStr = "'" + row[0] + "'" + ',' + "'" + row[1] + "'"
        sql = 'insert into ImagePath values({})'.format(tmpStr)
        logger.info(sql)
        return self.query.exec_(sql)

    #修改一个单元的数据
    def modifyOneCell(self,id,colName,value):
        value = "'{}'".format(value)
        sql = "update ImagePath set {1} = {2} where ID = {0}".format(str(id),str(colName),str(value))
        logger.info(sql)
        return self.query.exec_(sql)    

    #删除一条病例数据
    def delOneRow(self,id):
        sql = 'delete from ImagePath where ID = {}'.format(str(id))
        logger.info(sql)
        return self.query.exec_(sql)        
    #重载：插入一条数据，以id形式
    def insertOneNullRow(self,id:str):
        tmpstr = str(id) + ','
        for i in range(1):
            tmpstr += 'Null,'
        tmpstr = tmpstr.strip(',')
        sql = 'insert into ImagePath values({})'.format(str(tmpstr))
        logger.info(sql)
        return self.query.exec_(sql)

if __name__ == "__main__":
    tmpSql = ImagePathSql()
    logger.info(tmpSql.readAllRow())
    tmpSql.insertOneRow(['123','123'])
    tmpSql.modifyOneCell('123','影像路径',1)
